﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HostAssistantCore
{
    public class MySqlManger
    {
        /// <summary>
        /// 获取所有数据库信息
        /// </summary>
        /// <returns></returns>
        public static void GetMySqlInfos(string ip,string user,  string password, string port, string path)
        {
            DataTable mysqlinfos = new DataTable();//存储对象
            MySqlInfo mysqlinfo = null;//单个数据库信息
            MySqlDbHelper dbmysql = new MySqlDbHelper();//数据库操作对象
            dbmysql.Initialize(ip, "mysql", user, password, port);//初始化数据库连接
            List<string> cmysqls = new List<string>();//本地数据库
            List<string> accessmysqls = new List<string>();//sqlite数据库
            try
            {
                // 获取所有数据库名Schema.Name
                string get_schema_name_sql = "select schema_name,default_character_set_name from information_schema.schemata";
                DataSet ds_schema_name = dbmysql.GetDataSet(CommandType.Text, get_schema_name_sql, null);//执行语句
                if (ds_schema_name.Tables[0].Rows.Count != 0)
                {
                   
                   // 获取数据库sqlite存储的mysql数据库
                    string sql = "  select * from SqlInfo where   1=1   ";
                    List<SqlInfo> list = new List<SqlInfo>();         
                    SqlInfo SqlInfo = new SqlInfo();
                    list = Data<SqlInfo>.GetInstance().GetModelList(sql, SqlInfo);
                    list= list.Where(o => o.type == "mysql").ToList();
                    foreach (var  item in list)
                    {
                        string cname = item.Mdbname;
                        accessmysqls.Add(cname);
                    }

                  
                    //遍历数据
                   
                    DataTable dt_schema_name = ds_schema_name.Tables[0];
                    //遍历行
                    foreach (DataRow dr in dt_schema_name.Rows)
                    {
                        if (dr["schema_name"].ToString() == "mysql" || dr["schema_name"].ToString() == "information_schema" || dr["schema_name"].ToString() == "performance_schema")
                        {
                            continue;
                        }
                        string ctime = "";//时间
                        string cpassword = "";//密码
                       
                        //获取每个数据库的具体信息
                       
                        mysqlinfo = new MySqlInfo();
                        mysqlinfo.Mtime = "";
                        mysqlinfo.Mpass = "";
                        mysqlinfo.Mdbname = dr["schema_name"].ToString();//数据库名
                        mysqlinfo.Municode = dr["default_character_set_name"].ToString();//编码
                        mysqlinfo.Mpath = path + @"\" + mysqlinfo.Mdbname;//路径
                       
                         // 如果sqlite数据库存有该数据直接获取
                        
                        if (accessmysqls.Contains(mysqlinfo.Mdbname))
                        {
                            var ritem=  list.Where(o=>o.Mdbname== mysqlinfo.Mdbname).FirstOrDefault();
                          
                                ctime = ritem.Mtime.ToString(); 
                                cpassword = ritem.Mpass; 
                          
                        }



                        string get_time_sql = "SELECT TABLE_SCHEMA,TABLE_NAME,UPDATE_TIME FROM information_schema.tables where table_schema=?mdbname order by update_time desc";
                        MySql.Data.MySqlClient.MySqlParameter[] mysqlparameters = new MySql.Data.MySqlClient.MySqlParameter[1];
                        mysqlparameters[0] = new MySql.Data.MySqlClient.MySqlParameter("?mdbname", mysqlinfo.Mdbname);
                        DataSet ds_get_time = dbmysql.GetDataSet(CommandType.Text, get_time_sql, mysqlparameters);//执行语句
                        if (ds_get_time.Tables[0].Rows.Count != 0)
                        {
                            string cutime = ds_get_time.Tables[0].Rows[0]["update_time"].ToString();
                            if (cutime == "")
                            {

                                // 记录时间
                                var ritem = list.Where(o => o.Mdbname == mysqlinfo.Mdbname).FirstOrDefault();

                                if (ritem!=null)
                                {
                                  
                                        string passtime = ritem.Mtime.ToString();
                                        if (passtime != "" && passtime != "未记录")
                                        {
                                            mysqlinfo.Mtime = passtime;
                                        }
                                  
                                }
                            }
                            else
                            {
                                mysqlinfo.Mtime = cutime;
                            }
                        }
                        if (mysqlinfo.Mtime == "")
                        {
                           
                            //从数据库读取
                           
                            if (accessmysqls.Contains(mysqlinfo.Mdbname))
                            {
                                mysqlinfo.Mtime = ctime;
                            }
                            else
                            {
                                mysqlinfo.Mtime = "未记录";
                            }

                        }
                      
                        // 查看用户是否存在
                       
                        MySql.Data.MySqlClient.MySqlParameter[] user_mysqlparameters = new MySql.Data.MySqlClient.MySqlParameter[1];
                        user_mysqlparameters[0] = new MySql.Data.MySqlClient.MySqlParameter("?mdbname", mysqlinfo.Mdbname);
                        string get_user_sql = "select user from mysql.user where user=?mdbname";
                        DataSet ds_get_user = dbmysql.GetDataSet(CommandType.Text, get_user_sql, user_mysqlparameters);//执行语句
                        if (ds_get_user.Tables[0].Rows.Count != 0)
                        {
                            mysqlinfo.Muser = mysqlinfo.Mdbname;
                        }
                        else
                        {
                            mysqlinfo.Muser = "未绑定用户";
                        }
                       
                        //  获取用户密码
                        
                        if (mysqlinfo.Muser != "未绑定用户")
                        {
                            if (cpassword != "")
                            {
                                mysqlinfo.Mpass = cpassword;
                            }
                            else
                            {
                                mysqlinfo.Mpass = "加密";
                            }
                        }
                        else
                        {
                            mysqlinfo.Mpass = "未绑定用户";
                        }
                        
                        // 将数据存储到数据库
                      
                        if (accessmysqls.Contains(mysqlinfo.Mdbname))
                        {



                           //
                            // 更新数据库
                           
                            string update_mysql_sql = "update SqlInfo set muser=@muser,mpass=@mpass,mtime=@mtime,municode=@municode,mpath=@mpath where mdbname=@mdbname  and type=@type";
                         

                            SqlInfo updateSqlInfo = new SqlInfo();
                            updateSqlInfo.Muser = mysqlinfo.Muser;
                            updateSqlInfo.Mpass = mysqlinfo.Mpass;
                            updateSqlInfo.Mtime =  mysqlinfo.Mtime;
                            updateSqlInfo.Municode = mysqlinfo.Municode;

                            updateSqlInfo.Mpath = mysqlinfo.Mpath;
                            updateSqlInfo.Mdbname = mysqlinfo.Mdbname;
                            updateSqlInfo.type = "mysql";


                        Data<SqlInfo>.GetInstance().Update(update_mysql_sql, updateSqlInfo);








                        }
                        else
                        {
                           
                           //插入数据库
                           
                           
                            SqlInfo insertmysqlinfo = new SqlInfo();
                            insertmysqlinfo.Mdbname = mysqlinfo.Mdbname;
                            insertmysqlinfo.Muser = mysqlinfo.Muser;
                            insertmysqlinfo.Mpass = mysqlinfo.Mpass;
                            insertmysqlinfo.Mtime = mysqlinfo.Mtime;

                            insertmysqlinfo.Municode = mysqlinfo.Municode;
                            insertmysqlinfo.Mpath = mysqlinfo.Mpath;
                            

                            insertmysqlinfo.type = "mysql";

                            string query = "INSERT INTO SqlInfo(Municode,  Mpath,  type,Mdbname,Muser,Mpass,Mtime)VALUES(  @Municode,  @Mpath,  @type,@Mdbname,@Muser,@Mpass,@Mtime)   ";
                            Data<SqlInfo>.GetInstance().Add(query, insertmysqlinfo);



                        }

                        cmysqls.Add(mysqlinfo.Mdbname);//保存数据库名到本地
                    }  
                   
                    // 删除不存在的数据库
                   
                    foreach (var  arow in accessmysqls)
                    {
                        string cname = arow;
                        if (!cmysqls.Contains(cname))
                        {

                            //不存在删除
                            string delete_access_sql = " mdbname=@mdbname and type=@type ";
                            SqlInfo delSqlInfo = new SqlInfo();
                            delSqlInfo.Mdbname = cname;
                            delSqlInfo.type = "mysql";
                            Data<SqlInfo>.GetInstance().Delete("SqlInfo", delete_access_sql, delSqlInfo);



                          
                        }
                    }
                 
                }
                else
                {
                   
                    //未能获取到数据
                    
                }
            }
            catch (Exception ex)
            {
                NLogger.Error(ex.ToString());
                throw ex;
              // 不能执行语句
              
            }
           // return mysqlinfos;
        }
    
        #region 根据datatable获得列名     public static string[] GetColumnsByDataTable(DataTable dt)
        /// <summary>
        /// 根据datatable获得列名
        /// </summary>
        /// <param name="dt">表对象</param>
        /// <returns>返回结果的数据列数组</returns>
        public static string[] GetColumnsByDataTable(DataTable dt)
        {
            string[] strColumns = null;

            if (dt.Columns.Count > 0)
            {
                int columnNum = 0;
                columnNum = dt.Columns.Count;
                strColumns = new string[columnNum];
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    strColumns[i] = dt.Columns[i].ColumnName;
                }
            }
            return strColumns;
        }
        #endregion
     
        /// <summary>
        /// 根据数据库名返回数据库信息
        /// </summary>
        /// <param name="mysqlname"></param>
        /// <returns></returns>
        public static DataTable GetMySqlInfo(string mysqlname)
        {
            return null;
        }
        /// <summary>
        /// 设置Mysql数据库
        /// </summary>
        /// <returns></returns>
        public static int SetMySqlInfo()
        {
            return -1;
        }
        /// <summary>
        /// 创建数据库
        /// </summary>
        /// <returns></returns>
        public static int CreateMySql()
        {
            return -1;
        }
        /// <summary>
        /// 往存储添加新的数据库信息
        /// </summary>
        /// <returns></returns>
        public static DataTable AdoAddNewMysqlInfo()
        {
            return null;
        }
    }
}
